from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

# mysql地址及账号信息
msc ={
    'add'   : 'm10.n.rds.shawdo.com',
    'user'  : 'root',
    'passwd': 'qqqAAA0130',
    'table' : 'lnet_zqv1_shawdo',
    'port'  : '3306',
}

Base = automap_base()
engine = create_engine("mysql+mysqlconnector://{}:{}@{}:{}/{}".format(msc['user'],msc['passwd'],msc['add'],msc['port'],msc['table']),
    echo=False,
    pool_size=5,
    pool_recycle=120,
    pool_pre_ping=True,
    )
Base.prepare(engine, reflect=True)


# 读取一条数据
def get_apiv2_baotaip():
    # 初始化表
    apiv2_baotaip = Base.classes.apiv2_baotaip
    db = Session(engine)
    # 初始表结束

    # 查询单条数据
    bt = db.query(apiv2_baotaip).filter_by(id=3).one()
    return bt

    # 其他的数据库 增、删、改、查 参考 ---------------

    # 查 读取单条
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id == 3).one()
    # 查 读取多条
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id == 3)
    # 查 比对查询
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id >= '2').count()
    # 查 取反使用 ~ 运算符
    db.query(apiv2_baotaip).filter(~apiv2_baotaip.id >= '2').count()

    # 查 or and in
    db.query(apiv2_baotaip).filter(or_(apiv2_baotaip.id >= '2', apiv2_baotaip.id >= '4'))
    db.query(apiv2_baotaip).filter(and_(apiv2_baotaip.id >= '2', apiv2_baotaip.id >= '4'))
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id.in_([1,2,3]))

    # 查 多字段排序 group_by
    db.query(apiv2_baotaip).order_by(apiv2_baotaip.id.desc(), apiv2_baotaip.ip)
    db.query(apiv2_baotaip).group_by(apiv2_baotaip.id)

    # 增 增加一条数据
    addone = apiv2_baotaip(cdnid_id='1', ip='127.0.0.1')
    db.add(addone)
    db.commit()
    # 增 批量添加数据
    add_list = [
        apiv2_baotaip(cdnid_id='1', ip='127.0.0.1'),
        apiv2_baotaip(cdnid_id='1', ip='127.0.0.2'),
        apiv2_baotaip(cdnid_id='1', ip='127.0.0.3'),
    ]
    db.add_all(add_list)
    db.commit()

    # 修改数据
    apiv2_baotaip = db.query(apiv2_baotaip).filter(apiv2_baotaip.id == 3).one()
    apiv2_baotaip.ip = '127.0.0.1'
    db.commit()
    # 批量修改
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id == 3).update({"ip": "127.0.0.1"})

    # 删除
    db.query(apiv2_baotaip).filter(apiv2_baotaip.id == 3).delete()

